package com.atguigu.dbutils;

import com.atguigu.bean.Customer;
import com.atguigu.connection.util.JDBCUtil;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.*;
import org.junit.Test;

import java.sql.Connection;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

/**
 * commons-dbutils 是 Apache 组织提供的一个开源 JDBC 工具类库，封装了针对于数据库的增删改查操作
 *
 * @author admin
 */
public class QueryRunnerTest {

    /**
     * 测试插入
     */
    @Test
    public void testInsert() {

        Connection connection = null;
        try {
            QueryRunner runner = new QueryRunner();
            connection = JDBCUtil.getConnection3();
            String sql = "insert into customers(name, email, birth) value(?, ?, ?)";
            int insertCount = runner.update(connection, sql, "吴签", "wuqian@qq.com", "1983-07-15");
            System.out.println("添加了" + insertCount + "条记录");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 测试查询
     * BeanHandle：是 ResultSetHandler 接口的实现类，用于封装表中的一条数据
     *
     * @throws Exception
     */
    @Test
    public void testQuery1() {
        Connection connection = null;

        try {
            QueryRunner runner = new QueryRunner();
            connection = JDBCUtil.getConnection3();
            String sql = "select id, name, email, birth from customers where id = ?";
            BeanHandler<Customer> handler = new BeanHandler<>(Customer.class);
            Customer customer = runner.query(connection, sql, handler, 22);

            System.out.println(customer);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * BeanListHandler：是 ResultSetHandler 接口的实现类，用于封装表中的多条数据构成的集合
     *
     * @throws Exception
     */
    @Test
    public void testQuery2() {
        Connection connection = null;

        try {
            QueryRunner runner = new QueryRunner();
            connection = JDBCUtil.getConnection3();
            String sql = "select id, name, email, birth from customers where id < ?";
            BeanListHandler<Customer> handler = new BeanListHandler<>(Customer.class);
            List<Customer> customers = runner.query(connection, sql, handler, 22);

            customers.forEach(System.out::println);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * MapHandler：是 ResultSetHandler 接口的实现类，用于封装表中的一条数据
     * 将字段及相应字段的值作为 map 中的 key 和 value
     *
     * @throws Exception
     */
    @Test
    public void testQuery3() {
        Connection connection = null;

        try {
            QueryRunner runner = new QueryRunner();
            connection = JDBCUtil.getConnection3();
            String sql = "select id, name, email, birth from customers where id = ?";
            MapHandler handler = new MapHandler();
            Map<String, Object> map = runner.query(connection, sql, handler, 22);

            System.out.println(map);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * MapListHandler：是 ResultSetHandler 接口的实现类，用于封装表中的多条数据构成的集合
     * 将字段及相应字段的值作为 map 中的 key 和 value，将这些 map 添加到 List 中
     *
     * @throws Exception
     */
    @Test
    public void testQuery4() {
        Connection connection = null;

        try {
            QueryRunner runner = new QueryRunner();
            connection = JDBCUtil.getConnection3();
            String sql = "select id, name, email, birth from customers where id < ?";
            MapListHandler handler = new MapListHandler();
            List<Map<String, Object>> maps = runner.query(connection, sql, handler, 22);

            maps.forEach(System.out::println);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * ScalarHandler：用于查询特殊值
     */
    @Test
    public void testQuery5() {
        Connection connection = null;

        try {
            QueryRunner runner = new QueryRunner();
            connection = JDBCUtil.getConnection3();
            String sql = "select count(*) from customers";
            ScalarHandler handler = new ScalarHandler();
            Long count = (Long) runner.query(connection, sql, handler);

            System.out.println(count);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Test
    public void testQuery6() {
        Connection connection = null;

        try {
            QueryRunner runner = new QueryRunner();
            connection = JDBCUtil.getConnection3();
            String sql = "select Max(birth) from customers";
            ScalarHandler handler = new ScalarHandler();
            Date maxBirth = (Date) runner.query(connection, sql, handler);

            System.out.println(maxBirth);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 自定义 ResultSetHandler 的实现类
     */
    @Test
    public void testQuery7() {
        Connection connection = null;

        try {
            QueryRunner runner = new QueryRunner();
            connection = JDBCUtil.getConnection3();

            String sql = "select id, name, email, birth from customers where id = ?";
            ResultSetHandler<Customer> handler = new ResultSetHandler<Customer>() {
                @Override
                public Customer handle(ResultSet resultSet) throws SQLException {
                    // return null;
                    // return new Customer(12, "成龙", "Jacky@126.com", new Date(1245746577458L));

                    if (resultSet.next()) {
                        int id = resultSet.getInt("id");
                        String name = resultSet.getString("name");
                        String email = resultSet.getString("email");
                        Date birth = resultSet.getDate("birth");

                        Customer customer = new Customer(id, name, email, birth);
                        return customer;

                    }
                    return null;
                }
            };
            Customer customer = runner.query(connection, sql, handler, 22);
            System.out.println(customer);


        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}
